package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.model.Class;
import com.model.PaperInfo;
import com.util.DBUtil;
import com.util.Mode;
import com.util.ModelPaperInfo;

public class PaperInfoDao {
	Connection conn;
	PreparedStatement ps;
	ResultSet rs;
	
	public int insertAll(String name,String pd,PaperInfo p) {
		conn=DBUtil.getConn();
		int row=-1;
		String sql="";
		if(!pd.equals("修改")) {
			sql="insert into paperInfo values(null,"+p.getCurId()+",?,'"+p.getCreateTime()+"',0,1)";
		}else {
			sql="update paperInfo set isDelete=1 where PaperName=?";
		}
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int updateById(PaperInfo p) {
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("update paperInfo set CurId=?,PaperName=? where PaperId=?");
			ps.setInt(1, p.getCurId());
			ps.setString(2, p.getPaperName());
			ps.setInt(3, p.getPaperId());
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int deleteById(int id) {
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("update paperInfo set isdelete=0 where PaperId=?");
			ps.setInt(1, id);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int seelctCount(String name,int km){
		int count=0;
		conn=DBUtil.getConn();
		String sql="select count(*) from paperInfo,curriInfo where curriInfo.CurId=paperInfo.CurId and paperInfo.isdelete=1";
		if(name!=null&&!"".equals(name)&&km==0) {
			sql="select count(*) from paperInfo,curriInfo where curriInfo.CurId=paperInfo.CurId and paperInfo.isdelete=1 and PaperName like'%"+name+"%'";
		}else if(km!=0) {
			sql="select count(*) from paperInfo,curriInfo where curriInfo.CurId=paperInfo.CurId and paperInfo.isdelete=1 and curriInfo.CurId="+km+" and PaperName like'%"+name+"%'";
		}
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return count;
	}
	
	public ModelPaperInfo selectAll(int page,int rows,String name,int km) {
		ModelPaperInfo m=new ModelPaperInfo();
		ArrayList<PaperInfo> list=new ArrayList<PaperInfo>();
		conn=DBUtil.getConn();
		page=(page-1)*rows;
		String sql="select paperInfo.*,curriInfo.CurName from paperInfo,curriInfo where curriInfo.CurId=paperInfo.CurId and paperInfo.isdelete=1 limit ?,?";
		if(name!=null&&!"".equals(name)&&km==0) {
			sql="select paperInfo.*,curriInfo.CurName from paperInfo,curriInfo where curriInfo.CurId=paperInfo.CurId and paperInfo.isdelete=1 and PaperName like'%"+name+"%' limit ?,?";
		}else if(km!=0) {
			sql="select paperInfo.*,curriInfo.CurName from paperInfo,curriInfo where curriInfo.CurId=paperInfo.CurId and paperInfo.isdelete=1 and curriInfo.CurId="+km+" and PaperName like'%"+name+"%' limit ?,?";
		}
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, page);
			ps.setInt(2, rows);
			rs=ps.executeQuery();
			while(rs.next()) {
				PaperInfo c=new PaperInfo(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getInt(6),rs.getString(7));
				list.add(c);
			}
			m.setRows(list);
			m.setTotal(seelctCount(name,km));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return m;
	}
	

	public int selectIdByName(String name) {
		conn=DBUtil.getConn();
		int id=0;
		try {
			ps=conn.prepareStatement("select PaperId from paperInfo where PaperName=?");
			ps.setString(1, name);
			rs=ps.executeQuery();
			if(rs.next()) {
				id=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return id;
	}
	
	public int selectByname(String name) {
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("select count(*) from paperInfo where isDelete=0 and PaperName=?");
			ps.setString(1, name);
			rs=ps.executeQuery();
			if(rs.next()) {
				row=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public ArrayList<PaperInfo> selectIdNameByAll() {
		ArrayList<PaperInfo> list=new ArrayList<PaperInfo>();
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("select * from paperInfo where isDelete=1");
			rs=ps.executeQuery();
			while(rs.next()) {
				PaperInfo p=new PaperInfo();
				p.setPaperId(rs.getInt(1));
				p.setPaperName(rs.getString(3));
				list.add(p);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	public ArrayList<PaperInfo> selectnameById(int id) {
		ArrayList<PaperInfo> list=new ArrayList<PaperInfo>();
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("select * from paperInfo where isdelete=1 and CurId=?");
			ps.setInt(1, id);
			rs=ps.executeQuery();
			while(rs.next()) {
				PaperInfo p=new PaperInfo();
				p.setPaperId(rs.getInt(1));
				p.setPaperName(rs.getString(3));
				list.add(p);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	public int updatestateById(int id) {
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("update paperInfo set state=2 where PaperId=?");
			ps.setInt(1, id);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int selectIdBynames(String name) {
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("select paperid from paperInfo where paperName=?");
			ps.setString(1, name);
			rs=ps.executeQuery();
			if(rs.next()) {
				row=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
}
